﻿Namespace Muhasebe.Mahsup
	Public Module Functions

		Function Liste(ByVal Process As Token, ByVal yil As Integer, ByVal fis1 As Integer, ByVal fis2 As Integer)
			Q("select min(tarih) as tarih,fisno,min(fisacik) as aciklama,sum(borc) as borc,sum(alacak) as alacak")
			Q("from muhasebe..mahsup where year(tarih) = :yil and fisno between :fis1 and :fis2 group by fisno order by fisno")
			Return Process.OpenSQL("*", yil, fis1, fis2)
		End Function

		Function FisToplam(ByVal Process As Token, ByVal yil As Integer, ByVal fisno As Integer) As Recordset
			Return Process.OpenSQL("select min(fisacik) as aciklama,sum(borc) as toplam, min(tarih) as tarih from muhasebe..mahsup where year(tarih) = :yil and fisno = :fisno", yil, fisno)
		End Function

		Function FisDetayi(ByVal Process As Token, ByVal yil As Integer, ByVal fisno As Integer) As Recordset
			Process.DropTemporaryTables("fis")
			Q("select 0 as sirano, ltrim(m.hesapno) as hesapno,h.hesapadi,case when borc>0 then 'B' else 'A' end as ba,borc+alacak as tutar,convert(numeric(18,2),0) as toplam")
			Q("into #fis from muhasebe..mahsup m inner join muhasebe..hesplan h on m.hesapno = h.hesapno where year(tarih) = :yil and fisno = :fisno order by ba, m.hesapno")
			Process.ExecSQL("*", yil, fisno)
			Q("update #fis set toplam = (select sum(tutar) from #fis where substring(hesapno,1,1) = substring(a.hesapno,1,1) and ba = a.ba)")
			Q("from #fis a where	not exists(select * from #fis where substring(hesapno,1,1) = substring(a.hesapno,1,1) and hesapno > a.hesapno and ba = a.ba)")
			Process.ExecSQL("*")
			Process.ExecSQL("update #fis set tutar = 0 where tutar = toplam")
			Process.ExecSQL("update #fis set sirano = (select count(*) from #fis where hesapno <= a.hesapno and ba = a.ba) from #fis a")
			Q("select a.hesapno as hesapno1, a.hesapadi as hesapadi1, a.tutar as tutar1, a.toplam as toplam1,b.hesapno as hesapno2, b.hesapadi as hesapadi2, b.tutar as tutar2, b.toplam as toplam2")
			Q("from (select * from #fis where ba='B') a full outer join (select * from #fis where ba='A') b on a.sirano = b.sirano order by isnull(a.sirano,999), b.sirano")
			Return Process.OpenSQL("*")
		End Function

		Function FisOku(ByVal Process As Token, ByVal yil As Integer, ByVal fisno As Integer) As Recordset
			Q("select * from muhasebe..mahsup where year(tarih) = @yil and fisno = @fisno order by case when borc > 0 then 1 else 2 end,hesapno")
			Return Process.OpenSQL("*", yil, fisno)
		End Function

		Function SubeAktar(ByVal Process As Token, sube As String, tarih As Date) As Recordset
			Q("select * from (")
			Q("select hesappno as hesapno,sum(borc) as borc,sum(alacak) as alacak from sube" & sube & "..kmahsup where islemtar = @tarih and alacak = 0 group by hesappno union all")
			Q("select hesappno as hesapno,sum(borc) as borc,sum(alacak) as alacak from sube" & sube & "..kmahsup where islemtar = @tarih and borc = 0 group by hesappno) t")
			Q("order by case when borc > 0 then 1 else 2 end,hesapno")
			Return Process.OpenSQL("*", tarih)
		End Function

	End Module
End Namespace

